package jsyf.oa.dao.system;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.oa.entity.system.BigAchievementsEntity;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by Administrator on 2018/8/2 0002.
 */
@Repository
public class BigAchievementsDao extends BaseDao {

    public BigAchievementsDao() {
        super.iBaseEntity = new BigAchievementsEntity();
    }

    public List<BigAchievementsEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<BigAchievementsEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            BigAchievementsEntity entity = (BigAchievementsEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, BigAchievementsEntity.class);
    }

    public JSONObject getListByBigAreaId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_big_achievements.ID AS bigAchievementsId,\n" +
                "t_sys_user_achievements.ID AS userAchievementsId,\n" +
                "t_sys_big_achievements.BIG_AREA_ID AS bigAreaId,\n" +
                "t_sys_big_achievements.BIG_MANAGER_ID AS bigManagerId,\n" +
                "t_sys_big_achievements.`MONTH` AS month,\n" +
                "t_sys_big_achievements.SALE_TARGET_NUM AS saleTargetNum,\n" +
                "t_sys_big_achievements.SALE_FINISH_NUM AS saleFinishNum,\n" +
                "t_sys_big_achievements.CREATE_TIME AS createTime,\n" +
                "t_sys_big_achievements.CREATE_USER_ID AS createUserId,\n" +
                "t_sys_user_achievements.BIG_MANAGER_TARGER_NUM AS bigManagerTargerNum,\n" +
                "t_sys_user_achievements.MANAGER_TARGET_NUM AS managerTargerNum\n" +
                "FROM\n" +
                "t_sys_big_achievements\n" +
                "LEFT JOIN t_sys_user_achievements ON t_sys_big_achievements.BIG_AREA_ID=t_sys_user_achievements.BIG_AREA_ID\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        JSONObject records = super.queryForJsonObject(sql.toString());
        return records;
    }


    public List<JSONObject> getPeopleFineByCompanyId(String companyId, String month, String day) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "companyId,\n" +
                "\t companyName,\n" +
                "\tdepartId,\n" +
                " departName,\n" +
                " `departSortNo`,\n" +
                "roleName,\n" +
                "  roleId,\n" +
                " userId,\n" +
                " userName,\n" +
                " dayMoney,\n" +
                " monthMoney\n" +
                "FROM\n" +
                "(\n" +
                "SELECT\n" +
                "\tt_sys_company.ID AS companyId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_sys_depart.ID AS departId,\n" +
                "\tt_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role.SORT_NO AS roleSortNo,\n" +
                "\tt_sys_user.SORT_NO AS userSortNo,\n" +
                "\tCASE\n" +
                "WHEN `t_sys_depart`.`PARENT_ID` = '0' THEN\n" +
                "\t`t_sys_depart`.`SORT_NO` * 100\n" +
                "ELSE\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t`t_sys_depart_parent`.`SORT_NO`\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_sys_depart` `t_sys_depart_parent`\n" +
                "\t\tWHERE\n" +
                "\t\t\t`t_sys_depart_parent`.`ID` = `t_sys_depart`.`PARENT_ID`\n" +
                "\t) * 100 + `t_sys_depart`.`SORT_NO` + 1\n" +
                "END AS `departSortNo`,\n" +
                " IFNULL(t_sys_role_name.`NAME`, '') AS roleName,\n" +
                " IFNULL(t_sys_role_name.`ID`, '') AS roleId,\n" +
                " IFNULL(t_sys_user.`ID`, '') AS userId,\n" +
                " IFNULL(t_sys_user.`NAME`, '') AS userName,\n" +
                " IFNULL(dayMoney.money, 0) AS dayMoney,\n" +
                " IFNULL(monthMoney.mMoney, 0) AS monthMoney\n" +
                "FROM\n" +
                "t_sys_user\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name  ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_bonus ON t_sys_bonus.USER_ID = t_sys_user.ID\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tSUM(MONEY) AS money,\n" +
                "\t\tUSER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_sys_bonus\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND `DAY` = '" + day + "'\n" +
                "\tGROUP BY\n" +
                "\t\tUSER_ID\n" +
                ") AS dayMoney ON dayMoney.userId = t_sys_user.ID\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tSUM(MONEY) AS mMoney,\n" +
                "\t\tUSER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_sys_bonus\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND `DAY` LIKE '%" + month + "%'\n" +
                "\tGROUP BY\n" +
                "\t\tUSER_ID\n" +
                ") AS monthMoney ON monthMoney.userId = t_sys_user.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user.JOB_STATE <> 1\n" +
                "AND t_sys_company.ID = '" + companyId + "'\n" +
                "AND t_sys_role_name.`NAME` IN (\n" +
                "\t'大区经理',\n" +
                "\t'区域总监',\n" +
                "\t'销售总监',\n" +
                "\t'总经理'\n" +
                ")\n" +
                "AND t_sys_depart.DEL_FLAG = 0\n" +
                "AND t_sys_role.DEL_FLAG = 0\n" +
                "GROUP BY\n" +
                "\tt_sys_company.ID,\n" +
                "\tt_sys_depart.ID,\n" +
                "\tt_sys_role.ID,\n" +
                "\tt_sys_role_name.`ID`,\n" +
                "\tt_sys_user.ID\n" +
                "UNION \n" +
                "SELECT\n" +
                "\tt_sys_company.ID AS companyId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_sys_depart.ID AS departId,\n" +
                "\tt_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role.SORT_NO AS roleSortNo,\n" +
                "\tt_sys_user.SORT_NO AS userSortNo,\n" +
                "\tCASE\n" +
                "WHEN `t_sys_depart`.`PARENT_ID` = '0' THEN\n" +
                "\t`t_sys_depart`.`SORT_NO` * 100\n" +
                "ELSE\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\t`t_sys_depart_parent`.`SORT_NO`\n" +
                "\t\tFROM\n" +
                "\t\t\t`t_sys_depart` `t_sys_depart_parent`\n" +
                "\t\tWHERE\n" +
                "\t\t\t`t_sys_depart_parent`.`ID` = `t_sys_depart`.`PARENT_ID`\n" +
                "\t) * 100 + `t_sys_depart`.`SORT_NO` + 1\n" +
                "END AS `departSortNo`,\n" +
                " IFNULL(t_sys_role_name.`NAME`, '') AS roleName,\n" +
                " IFNULL(t_sys_role_name.`ID`, '') AS roleId,\n" +
                " IFNULL(t_sys_user.`ID`, '') AS userId,\n" +
                " IFNULL(t_sys_user.`NAME`, '') AS userName,\n" +
                " IFNULL(dayMoney.money, 0) AS dayMoney,\n" +
                " IFNULL(monthMoney.mMoney, 0) AS monthMoney\n" +
                "FROM\n" +
                "t_sys_user\n" +
                "LEFT JOIN t_sys_user_2_role ON t_sys_user_2_role.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user_2_role.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name  ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_bonus ON t_sys_bonus.USER_ID = t_sys_user.ID\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tSUM(MONEY) AS money,\n" +
                "\t\tUSER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_sys_bonus\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND `DAY` = '" + day + "'\n" +
                "\tGROUP BY\n" +
                "\t\tUSER_ID\n" +
                ") AS dayMoney ON dayMoney.userId = t_sys_user.ID\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tSUM(MONEY) AS mMoney,\n" +
                "\t\tUSER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_sys_bonus\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND `DAY` LIKE '%" + month + "%'\n" +
                "\tGROUP BY\n" +
                "\t\tUSER_ID\n" +
                ") AS monthMoney ON monthMoney.userId = t_sys_user.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user.JOB_STATE <> 1\n" +
                "AND t_sys_company.ID = '" + companyId + "'\n" +
                "AND t_sys_role_name.`NAME` IN (\n" +
                "\t'大区经理',\n" +
                "\t'区域总监',\n" +
                "\t'销售总监',\n" +
                "\t'总经理'\n" +
                ")\n" +
                "AND t_sys_depart.DEL_FLAG = 0\n" +
                "AND t_sys_role.DEL_FLAG = 0\n" +
                "GROUP BY\n" +
                "\tt_sys_company.ID,\n" +
                "\tt_sys_depart.ID,\n" +
                "\tt_sys_role.ID,\n" +
                "\tt_sys_role_name.`ID`,\n" +
                "\tt_sys_user.ID\n" +
                ") AS main\n" +
                "ORDER BY\n" +
                "\tdepartSortNo,\n" +
                " roleSortNo,\n" +
                " userSortNo");
        List<JSONObject> list = super.queryForJsonList(sql.toString());
        return list;
    }

    public List<JSONObject> getRoleIdByBigAreaIdAndRoleName(String bigAreaId, String roleName) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                " t_sys_role.ID AS roleId\n" +
                "FROM \n" +
                "t_sys_depart\n" +
                "LEFT JOIN t_sys_role ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE\n" +
                "1=1\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n");
        sql.append(" AND t_sys_depart.`NAME` IN (SELECT name from t_sys_region WHERE ID = '" + bigAreaId + "')\n");
        sql.append("AND t_sys_role_name.`NAME`= '" + roleName + "'\n");
        sql.append("GROUP BY  t_sys_role.ID ");
        List<JSONObject> roleIdList = super.queryForJsonList(sql.toString());
        return roleIdList;
    }

    public JSONObject getBigRegionTargetBycompanyId(String departName, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_region.ID as id,\n" +
                "\tt_sys_region.`NAME` as name\n" +
                "FROM\n" +
                "\tt_sys_region\n" +
                "LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID = t_sys_company.ID\n" +
                "WHERE\n" +
                "  t_sys_region.DEL_FLAG=0 \n" +
                " AND t_sys_region.`NAME`= ?\n" +
                " AND t_sys_company.ID = ? " +
                "\n" +
                "\n");
        return super.queryForJsonObject(sql.toString(), new Object[]{departName, companyId});
    }

    public BigDecimal getSelFinishNumByBigAreaId(String bigAreaId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "IFNULL(SALE_FINISH_NUM,0.00)AS saleFinishNum\n" +
                "FROM\n" +
                "t_sys_big_achievements\n" +
                "WHERE 1=1\n" +
                "AND BIG_AREA_ID='" + bigAreaId + "'");
        JSONObject records = super.queryForJsonObject(sql.toString());
        BigDecimal sareFinishNum = (new BigDecimal("0"));
        if (records != null) {
            sareFinishNum = records.getBigDecimal("sareFinishNum");
        }
        return sareFinishNum;
    }

    public List<JSONObject> getForGroupVacantAndFine(String condition, String month, String day) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "syst.companyId,\n" +
                "syst.companyName,\n" +
                "syst.regionId,\n" +
                "syst.regionName,\n" +
                "syst.userId,\n" +
                "syst.userName,\n" +
                "syst.roleName,\n" +
                "syst.currentRole,\n" +
                "syst.roleSortNo,\n" +
                "syst.userSortNo,\n" +
                "syst.money,\n" +
                "syst.managerTargetNum,\n" +
                "syst.bigManagerTargetNum,\n" +
                "syst.managerCount,\n" +
                "syst.bigManagerCount,\n" +
                "syst.bigManagerVacant,\n" +
                "syst.managerVacant,\n" +
                "syst.companySortNo,\n" +
                "syst.regionSortNo\n" +
                "FROM\n" +
                "(SELECT\n" +
                "sysBig.companyId,\n" +
                "sysBig.companyName,\n" +
                "sysBig.regionId,\n" +
                "sysBig.regionName,\n" +
                "sysBig.userId,\n" +
                "sysBig.userName,\n" +
                "sysBig.roleName,\n" +
                "CASE\n" +
                " WHEN '大区经理' in (SELECT roleName FROM v_sys_user WHERE id=sysBig.userId)\n" +
                "THEN 'true'\n" +
                "ELSE 'false' \n" +
                "END\n" +
                "AS currentRole,\n" +
                "sysBig.roleSortNo,\n" +
                "sysBig.userSortNo,\n" +
                "sysBig.money,\n" +
                "sysBig.managerTargetNum,\n" +
                "sysBig.bigManagerTargetNum,\n" +
                "sysBig.managerCount,\n" +
                "sysBig.bigManagerCount,\n" +
                "sysBig.bigManagerVacant,\n" +
                "sysBig.managerVacant,\n" +
                "sysBig.companySortNo,\n" +
                "sysBig.regionSortNo\n" +
                "FROM(\n" +
                "SELECT \n" +
                "t_sys_company.ID as companyId ,\n" +
                "t_sys_company.`NAME` as companyName,\n" +
                "t_sys_region.ID as regionId,\n" +
                "t_sys_region.`NAME` as regionName,\n" +
                "v_sys_user.id as userId,\n" +
                "v_sys_user.`name` as userName,\n" +
                "CASE\n" +
                " WHEN v_sys_user.roleName='大区经理'\n" +
                "THEN v_sys_user.roleName\n" +
                "ELSE t_sys_bonus.ROLE_NAME \n" +
                "END\n" +
                "AS roleName,\n" +
                "v_sys_user.roleSortNo as roleSortNo,\n" +
                "v_sys_user.userSortNo AS userSortNo,\n" +
                "IFNULL(t_sys_bonus.MONEY,0) as money,\n" +
                "IFNULL(achieve.MANAGER_TARGET_NUM,0) AS managerTargetNum,\n" +
                "IFNULL(achieve.BIG_MANAGER_TARGER_NUM,0) AS bigManagerTargetNum,\n" +
                "IFNULL(user2Region.managerCount,0) AS managerCount,\n" +
                "IFNULL(user2BigRegion.bigManagerCount,0) AS bigManagerCount,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0) >0\n" +
                "\t\t THEN\n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "AS bigManagerVacant,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)>0\n" +
                "\t THEN\n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                " AS managerVacant,\n" +
                "t_sys_company.SORT_NO AS companySortNo,\n" +
                "t_sys_region.SORT_NO AS regionSortNo\n" +
                "FROM t_sys_company \n" +
                "LEFT JOIN t_sys_region ON t_sys_company.ID = t_sys_region.COMPANY_ID AND t_sys_region.DEL_FLAG = 0 AND t_sys_region.PARENT_ID = '0' \n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID \n" +
                "LEFT JOIN v_sys_user ON t_sys_user_2_region.USER_ID = v_sys_user.id AND t_sys_region.`NAME` = v_sys_user.departName  AND v_sys_user.jobState <> 1\n" +
                "LEFT JOIN (\n" +
                "\tSELECT COMPANY_ID,BIG_REGION_ID,USER_ID,ROLE_NAME,SUM(MONEY) as money FROM t_sys_bonus  WHERE `DAY` like '"+month+"%' AND TYPE = 0 AND ROLE_NAME = '大区经理'\n" +
                " GROUP BY COMPANY_ID,BIG_REGION_ID,USER_ID,ROLE_NAME\n" +
                ") as t_sys_bonus\n" +
                "ON t_sys_company.ID = t_sys_bonus.COMPANY_ID and  t_sys_region.ID = t_sys_bonus.BIG_REGION_ID AND v_sys_user.id = t_sys_bonus.USER_ID\n" +
                "\n" +
                "LEFT JOIN t_sys_user_achievements AS achieve  ON t_sys_region.ID=achieve.BIG_AREA_ID\n" +
                "LEFT JOIN (\n" +
                "SELECT \n" +
                "COUNT(1) AS managerCount,\n" +
                "REGION_ID AS regionId,\n" +
                "t_sys_user.name \n" +
                "FROM t_sys_user_2_region \n" +
                "LEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1\n" +
                " AND t_sys_role_name.`NAME` in ('区域经理','区域组长')\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "GROUP BY t_sys_user_2_region.REGION_ID\n" +
                ") AS user2Region ON  user2Region.regionId=t_sys_region.ID\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "\t\t\tCOUNT(1) AS bigManagerCount,\n" +
                "\t\tt_sys_depart.`NAME` AS departName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_part_time_for_report\n" +
                "\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON v_sys_part_time_for_report.departId = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tLEFT JOIN t_sys_user ON v_sys_part_time_for_report.userId = t_sys_user.ID\n" +
                "\t\tWHERE 1=1\n" +
                "\t\tAND v_sys_part_time_for_report.userId IS NOT NULL\n" +
                "\t\tAND t_sys_role_name.`NAME` ='大区经理'\n" +
                "\t\tAND t_sys_depart.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "GROUP BY t_sys_depart.ID\n" +
                ") AS user2BigRegion ON   user2BigRegion.`departName`=t_sys_region.`NAME`\n" +
                "WHERE 1 = 1 \n" +
                "AND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA' \n" +
                "AND t_sys_company.ID not in('2017121617484A2796D8F52B6B558B94') \n" +
                "AND t_sys_company.DEL_FLAG = 0 \n" +
                "AND ( \n" +
                "v_sys_user.roleName = '大区经理'\n" +
                "OR ('大区经理' NOT in (SELECT t.roleName FROM v_sys_user AS t WHERE t.id=v_sys_user.id) AND t_sys_bonus.ROLE_NAME='大区经理')\n" +
                ")\n" +
                "AND v_sys_user.id IS NOT NULL\n" +
                "AND v_sys_user.companyId=t_sys_company.ID\n" +
                "GROUP BY\n" +
                "t_sys_company.ID,\n" +
                " t_sys_region.ID,\n" +
                "v_sys_user.id,\n" +
                "roleName) AS sysBig\n" +
                "UNION\n" +
                "SELECT\n" +
                "sysMain.companyId,\n" +
                "sysMain.companyName,\n" +
                "sysMain.regionId,\n" +
                "sysMain.regionName,\n" +
                "sysMain.userId,\n" +
                "sysMain.userName,\n" +
                "sysMain.roleName,\n" +
                "CASE\n" +
                " WHEN '区域总监' in (SELECT roleName FROM v_sys_user WHERE id=sysMain.userId)\n" +
                "THEN 'true'\n" +
                "ELSE 'false' \n" +
                "END\n" +
                "AS currentRole,\n" +
                "sysMain.roleSortNo,\n" +
                "sysMain.userSortNo,\n" +
                "sysMain.money,\n" +
                "SUM(sysMain.managerTargetNum) AS managerTargetNum,\n" +
                "SUM(sysMain.bigManagerTargetNum) AS bigManagerTargetNum,\n" +
                "SUM(sysMain.managerCount) AS managerCount,\n" +
                "SUM(sysMain.bigManagerCount) AS bigManagerCount,\n" +
                "SUM(sysMain.bigManagerVacant) AS bigManagerVacant,\n" +
                "SUM(sysMain.managerVacant) AS managerVacant,\n" +
                "sysMain.companySortNo,\n" +
                "sysMain.regionSortNo\n" +
                "FROM \n" +
                "(\n" +
                "SELECT \n" +
                "t_sys_company.ID as companyId ,\n" +
                "t_sys_company.`NAME` as companyName,\n" +
                "NULL AS regionId,\n" +
                "NULL AS regionName,\n" +
                "v_sys_user.id as userId,\n" +
                "v_sys_user.`name` as userName,\n" +
                "CASE\n" +
                " WHEN v_sys_user.roleName='区域总监'\n" +
                "THEN v_sys_user.roleName\n" +
                "ELSE t_sys_bonus.ROLE_NAME \n" +
                "END\n" +
                "AS roleName,\n" +
                "v_sys_user.roleSortNo AS roleSortNo,\n" +
                "v_sys_user.userSortNo AS userSortNo,\n" +
                "IFNULL(t_sys_bonus.money,0) as money,\n" +
                "IFNULL(achieve.MANAGER_TARGET_NUM,0) AS managerTargetNum,\n" +
                "IFNULL(achieve.BIG_MANAGER_TARGER_NUM,0) AS bigManagerTargetNum,\n" +
                "IFNULL(user2Region.managerCount,0) AS managerCount,\n" +
                "IFNULL(user2BigRegion.bigManagerCount,0) AS bigManagerCount,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0) >0\n" +
                "\t\t THEN\n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "AS bigManagerVacant,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)>0\n" +
                "\t THEN\n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                " AS managerVacant,\n" +
                "\n" +
                "t_sys_company.SORT_NO AS companySortNo,\n" +
                "800 AS regionSortNo\n" +
                "FROM t_sys_company \n" +
                "LEFT JOIN v_sys_user ON t_sys_company.ID = v_sys_user.companyId AND v_sys_user.jobState <> 1 \n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN (\n" +
                "\tSELECT \n" +
                "COMPANY_ID,\n" +
                "USER_ID, \n" +
                "ROLE_NAME,\n" +
                "SUM(MONEY) as money\n" +
                " FROM t_sys_bonus  \n" +
                "WHERE `DAY` like '"+month+"%'\n" +
                " AND TYPE = 0 AND ROLE_NAME in ('区域总监','销售总监')\n" +
                " GROUP BY \n" +
                "COMPANY_ID,\n" +
                "USER_ID\n" +
                ") as t_sys_bonus\n" +
                "ON t_sys_company.ID = t_sys_bonus.COMPANY_ID and v_sys_user.id = t_sys_bonus.USER_ID \n" +
                "LEFT JOIN t_sys_user_achievements AS achieve  ON t_sys_region.ID=achieve.BIG_AREA_ID\n" +
                "LEFT JOIN (\n" +
                "SELECT \n" +
                "COUNT(1) AS managerCount,\n" +
                "REGION_ID AS regionId,\n" +
                "t_sys_user.name \n" +
                "FROM t_sys_user_2_region \n" +
                "LEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1\n" +
                " AND t_sys_role_name.`NAME` in ('区域经理','区域组长')\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "GROUP BY t_sys_user_2_region.REGION_ID\n" +
                ") AS user2Region ON  user2Region.regionId=t_sys_region.ID\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "\t\t\tCOUNT(1) AS bigManagerCount,\n" +
                "\t\tt_sys_depart.`NAME` AS departName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_part_time_for_report\n" +
                "\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON v_sys_part_time_for_report.departId = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tLEFT JOIN t_sys_user ON v_sys_part_time_for_report.userId = t_sys_user.ID\n" +
                "\t\tWHERE 1=1\n" +
                "\t\tAND v_sys_part_time_for_report.userId IS NOT NULL\n" +
                "\t\tAND t_sys_role_name.`NAME` ='大区经理'\n" +
                "\t\tAND t_sys_depart.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "GROUP BY t_sys_depart.ID\n" +
                ") AS user2BigRegion ON   user2BigRegion.`departName`=t_sys_region.`NAME`\n" +
                "WHERE 1 = 1 \n" +
                "AND ( \n" +
                "v_sys_user.roleName = '区域总监'\n" +
                "OR ('区域总监' NOT in (SELECT t.roleName FROM v_sys_user AS t WHERE t.id=v_sys_user.id) AND t_sys_bonus.ROLE_NAME='区域总监')\n" +
                ")\n" +
                "AND t_sys_company.DEL_FLAG = 0 \n" +
                "AND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA' \n" +
                "AND t_sys_company.ID not in('2017121617484A2796D8F52B6B558B94') \n" +
                "AND t_sys_region.PARENT_ID=0\n" +
                "AND v_sys_user.id IS NOT NULL\n" +
                "AND v_sys_user.companyId=t_sys_company.ID\n" +
                ") AS sysMain\n" +
                "GROUP BY\n" +
                "sysMain.companyId,\n" +
                "sysMain.userId\n" +
                "UNION\n" +
                "SELECT\n" +
                "sysMainX.companyId,\n" +
                "sysMainX.companyName,\n" +
                "sysMainX.regionId,\n" +
                "sysMainX.regionName,\n" +
                "sysMainX.userId,\n" +
                "sysMainX.userName,\n" +
                "sysMainX.roleName,\n" +
                "CASE\n" +
                " WHEN '销售总监' in (SELECT roleName FROM v_sys_user WHERE id=sysMainX.userId)\n" +
                "THEN 'true'\n" +
                "ELSE 'false' \n" +
                "END\n" +
                "AS currentRole,\n" +
                "sysMainX.roleSortNo,\n" +
                "sysMainX.userSortNo,\n" +
                "sysMainX.money,\n" +
                "SUM(sysMainX.managerTargetNum) AS managerTargetNum,\n" +
                "SUM(sysMainX.bigManagerTargetNum) AS bigManagerTargetNum,\n" +
                "SUM(sysMainX.managerCount) AS managerCount,\n" +
                "SUM(sysMainX.bigManagerCount) AS bigManagerCount,\n" +
                "SUM(sysMainX.bigManagerVacant) AS bigManagerVacant,\n" +
                "SUM(sysMainX.managerVacant) AS managerVacant,\n" +
                "sysMainX.companySortNo,\n" +
                "sysMainX.regionSortNo\n" +
                "FROM \n" +
                "(\n" +
                "SELECT \n" +
                "t_sys_company.ID as companyId ,\n" +
                "t_sys_company.`NAME` as companyName,\n" +
                "NULL AS regionId,\n" +
                "NULL AS regionName,\n" +
                "v_sys_user.id as userId,\n" +
                "v_sys_user.`name` as userName,\n" +
                "CASE\n" +
                " WHEN v_sys_user.roleName='销售总监'\n" +
                "THEN v_sys_user.roleName\n" +
                "ELSE t_sys_bonus.ROLE_NAME \n" +
                "END\n" +
                "AS roleName,\n" +
                "v_sys_user.roleSortNo+2 AS roleSortNo,\n" +
                "v_sys_user.userSortNo AS userSortNo,\n" +
                "IFNULL(t_sys_bonus.MONEY,0) as money,\n" +
                "IFNULL(achieve.MANAGER_TARGET_NUM,0) AS managerTargetNum,\n" +
                "IFNULL(achieve.BIG_MANAGER_TARGER_NUM,0) AS bigManagerTargetNum,\n" +
                "IFNULL(user2Region.managerCount,0) AS managerCount,\n" +
                "IFNULL(user2BigRegion.bigManagerCount,0) AS bigManagerCount,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0) >0\n" +
                "\t\t THEN\n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "AS bigManagerVacant,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)>0\n" +
                "\t THEN\n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                " AS managerVacant,\n" +
                "t_sys_company.SORT_NO AS companySortNo,\n" +
                "1000 AS regionSortNo\n" +
                "FROM t_sys_company \n" +
                "LEFT JOIN t_sys_region ON t_sys_region.COMPANY_ID=t_sys_company.ID AND t_sys_region.PARENT_ID=0 AND t_sys_region.DEL_FLAG=0\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "LEFT JOIN v_sys_user ON t_sys_user_2_region.USER_ID = v_sys_user.id AND v_sys_user.jobState <> 1 \n" +
                "LEFT JOIN (\n" +
                "\tSELECT COMPANY_ID,USER_ID, ROLE_NAME,SUM(MONEY) as money FROM t_sys_bonus  WHERE `DAY` like '"+month+"%' AND TYPE = 0 AND ROLE_NAME in ('销售总监','区域总监') \n" +
                "GROUP BY COMPANY_ID,USER_ID\n" +
                ") as t_sys_bonus\n" +
                "ON t_sys_company.ID = t_sys_bonus.COMPANY_ID and v_sys_user.id = t_sys_bonus.USER_ID\n" +
                "LEFT JOIN t_sys_user_achievements AS achieve  ON t_sys_region.ID=achieve.BIG_AREA_ID\n" +
                "LEFT JOIN (\n" +
                "SELECT \n" +
                "COUNT(1) AS managerCount,\n" +
                "REGION_ID AS regionId,\n" +
                "t_sys_user.name \n" +
                "FROM t_sys_user_2_region \n" +
                "LEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1\n" +
                " AND t_sys_role_name.`NAME` in ('区域经理','区域组长')\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "GROUP BY t_sys_user_2_region.REGION_ID\n" +
                ") AS user2Region ON  user2Region.regionId=t_sys_region.ID\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "\t\t\tCOUNT(1) AS bigManagerCount,\n" +
                "\t\tt_sys_depart.`NAME` AS departName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_part_time_for_report\n" +
                "\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON v_sys_part_time_for_report.departId = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tLEFT JOIN t_sys_user ON v_sys_part_time_for_report.userId = t_sys_user.ID\n" +
                "\t\tWHERE 1=1\n" +
                "\t\tAND v_sys_part_time_for_report.userId IS NOT NULL\n" +
                "\t\tAND t_sys_role_name.`NAME` ='大区经理'\n" +
                "\t\tAND t_sys_depart.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "GROUP BY t_sys_depart.ID\n" +
                ") AS user2BigRegion ON   user2BigRegion.`departName`=t_sys_region.`NAME`\n" +
                "WHERE 1 = 1 \n" +
                "AND ( \n" +
                "v_sys_user.roleName = '销售总监'\n" +
                "OR ('销售总监' NOT in (SELECT t.roleName FROM v_sys_user AS t WHERE t.id=v_sys_user.id) AND t_sys_bonus.ROLE_NAME='销售总监')\n" +
                ")\n" +
                "AND t_sys_company.DEL_FLAG = 0 \n" +
                "AND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA' \n" +
                "AND t_sys_company.ID not in('2017121617484A2796D8F52B6B558B94')\n" +
                "AND v_sys_user.id IS NOT NULL\n" +
                "AND v_sys_user.companyId=t_sys_company.ID\n" +
                "GROUP BY\n" +
                "t_sys_company.ID,\n" +
                "t_sys_region.ID,\n" +
                "v_sys_user.id\n" +
                "ORDER BY\n" +
                "t_sys_company.SORT_NO ,\n" +
                "t_sys_region.SORT_NO\n" +
                ") AS sysMainX\n" +
                "GROUP BY\n" +
                "sysMainX.companyId,\n" +
                "sysMainX.userId\n" +
                "UNION\n" +
                "SELECT\n" +
                "sysMainZ.companyId,\n" +
                "sysMainZ.companyName,\n" +
                "sysMainZ.regionId,\n" +
                "sysMainZ.regionName,\n" +
                "sysMainZ.userId,\n" +
                "sysMainZ.userName,\n" +
                "sysMainZ.roleName,\n" +
                "CASE\n" +
                " WHEN '总经理' in (SELECT roleName FROM v_sys_user WHERE id=sysMainZ.userId)\n" +
                "THEN 'true'\n" +
                "ELSE 'false' \n" +
                "END\n" +
                "AS currentRole,\n" +
                "sysMainZ.roleSortNo,\n" +
                "sysMainZ.userSortNo,\n" +
                "sysMainZ.money,\n" +
                "SUM(sysMainZ.managerTargetNum) AS managerTargetNum,\n" +
                "SUM(sysMainZ.bigManagerTargetNum) AS bigManagerTargetNum,\n" +
                "SUM(sysMainZ.managerCount) AS managerCount,\n" +
                "SUM(sysMainZ.bigManagerCount) AS bigManagerCount,\n" +
                "SUM(sysMainZ.bigManagerVacant) AS bigManagerVacant,\n" +
                "SUM(sysMainZ.managerVacant) AS managerVacant,\n" +
                "sysMainZ.companySortNo,\n" +
                "sysMainZ.regionSortNo\n" +
                "FROM \n" +
                "(\n" +
                "SELECT \n" +
                "t_sys_company.ID as companyId ,\n" +
                "t_sys_company.`NAME` as companyName,\n" +
                "NULL AS regionId,\n" +
                "NULL AS regionName,\n" +
                "v_sys_user.id as userId,\n" +
                "v_sys_user.`name` as userName,\n" +
                "CASE\n" +
                " WHEN v_sys_user.roleName='总经理'\n" +
                "THEN v_sys_user.roleName\n" +
                "ELSE t_sys_bonus.ROLE_NAME \n" +
                "END\n" +
                "AS roleName,\n" +
                "v_sys_user.roleSortNo AS roleSortNo,\n" +
                "v_sys_user.userSortNo AS userSortNo,\n" +
                "IFNULL(t_sys_bonus.MONEY,0) as money,\n" +
                "IFNULL(achieve.MANAGER_TARGET_NUM,0) AS managerTargetNum,\n" +
                "IFNULL(achieve.BIG_MANAGER_TARGER_NUM,0) AS bigManagerTargetNum,\n" +
                "IFNULL(user2Region.managerCount,0) AS managerCount,\n" +
                "IFNULL(user2BigRegion.bigManagerCount,0) AS bigManagerCount,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0) >0\n" +
                "\t\t THEN\n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "AS bigManagerVacant,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)>0\n" +
                "\t THEN\n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                " AS managerVacant,\n" +
                "t_sys_company.SORT_NO AS companySortNo,\n" +
                "1000 AS regionSortNo\n" +
                "FROM t_sys_company \n" +
                "LEFT JOIN v_sys_user ON t_sys_company.ID = v_sys_user.companyId AND v_sys_user.jobState <> 1 \n" +
                "LEFT JOIN (\n" +
                "\tSELECT COMPANY_ID,USER_ID, ROLE_NAME,SUM(MONEY) as money FROM t_sys_bonus  WHERE `DAY` like '"+month+"%' AND TYPE = 0 AND ROLE_NAME ='总经理' GROUP BY COMPANY_ID,USER_ID\n" +
                ") as t_sys_bonus\n" +
                "ON t_sys_company.ID = t_sys_bonus.COMPANY_ID and v_sys_user.id = t_sys_bonus.USER_ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.COMPANY_ID=t_sys_company.ID AND t_sys_region.PARENT_ID=0 AND t_sys_region.DEL_FLAG=0\n" +
                "LEFT JOIN t_sys_user_achievements AS achieve  ON t_sys_region.ID=achieve.BIG_AREA_ID\n" +
                "LEFT JOIN (\n" +
                "SELECT \n" +
                "COUNT(1) AS managerCount,\n" +
                "REGION_ID AS regionId,\n" +
                "t_sys_user.name \n" +
                "FROM t_sys_user_2_region \n" +
                "LEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1\n" +
                " AND t_sys_role_name.`NAME` in ('区域经理','区域组长')\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "GROUP BY t_sys_user_2_region.REGION_ID\n" +
                ") AS user2Region ON  user2Region.regionId=t_sys_region.ID\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "\t\t\tCOUNT(1) AS bigManagerCount,\n" +
                "\t\tt_sys_depart.`NAME` AS departName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_part_time_for_report\n" +
                "\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON v_sys_part_time_for_report.departId = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tLEFT JOIN t_sys_user ON v_sys_part_time_for_report.userId = t_sys_user.ID\n" +
                "\t\tWHERE 1=1\n" +
                "\t\tAND v_sys_part_time_for_report.userId IS NOT NULL\n" +
                "\t\tAND t_sys_role_name.`NAME` ='大区经理'\n" +
                "\t\tAND t_sys_depart.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "GROUP BY t_sys_depart.ID\n" +
                ") AS user2BigRegion ON   user2BigRegion.`departName`=t_sys_region.`NAME`\n" +
                "WHERE 1 = 1 \n" +
                "AND ( \n" +
                "v_sys_user.roleName = '总经理'\n" +
                "OR ('总经理' NOT in (SELECT t.roleName FROM v_sys_user AS t WHERE t.id=v_sys_user.id) AND t_sys_bonus.ROLE_NAME='总经理')\n" +
                ")\n" +
                "AND t_sys_company.DEL_FLAG = 0 \n" +
                "AND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA' \n" +
                "AND t_sys_company.ID not in('2017121617484A2796D8F52B6B558B94')\n" +
                "AND v_sys_user.id IS NOT NULL\n" +
                "AND v_sys_user.companyId=t_sys_company.ID\n" +
                "GROUP BY\n" +
                "t_sys_company.ID,\n" +
                "t_sys_region.ID,\n" +
                "v_sys_user.id\n" +
                "ORDER BY\n" +
                "t_sys_company.SORT_NO ,\n" +
                "t_sys_region.SORT_NO\n" +
                ") AS sysMainZ\n" +
                "GROUP BY\n" +
                "sysMainZ.companyId,\n" +
                "sysMainZ.userId,\n" +
                "sysMainZ.roleName) AS syst\n" +
                " WHERE 1=1\n" );
        sql.append(condition);
        sql.append("GROUP BY\n" +
                "companyId,\n" +
                "regionId,\n" +
                "userId,\n" +
                "roleName\n" +
                "ORDER BY\n" +
                "companySortNo,\n" +
                "regionSortNo,\n" +
                "roleSortNo DESC,\n" +
                "userSortNo DESC\n");
        List<JSONObject> list = super.queryForJsonList(sql.toString());
        return list;
    }

    public JSONObject getRoleChangeDate(String companyId,String userId,String roleName){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "MAX(`DAY`) AS day\n" +
                "FROM\n" +
                "t_sys_bonus\n" +
                "WHERE 1=1\n" +
                "AND COMPANY_ID='"+companyId+"'\n" +
                "AND USER_ID='"+userId+"'\n" +
                "AND ROLE_NAME='"+roleName+"'");
        JSONObject records = super.queryForJsonObject(sql.toString());
        return records;
    }
}

